﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NET;
using MySql.Data.MySqlClient;

/// <summary>
/// Descripción breve de UsuariosBL
/// </summary>
/// 

    public class UsuariosBL
    {

        public List<string> ObtenerJefes()
        {
            var usuarios = new List<string> ();
            var query = "SELECT * FROM Usuarios WHERE rol ='3'";
            var myConnection = new MySqlConnection();
            myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
            myConnection.Open();

            var cmd = new MySqlCommand(query, myConnection);
            cmd.ExecuteNonQuery();
            var dataReader = cmd.ExecuteReader();
            //Leer datos de los usuarios
            while (dataReader.Read())
            {
                usuarios.Add((string)dataReader["usuarios_id"]);
            }

            dataReader.Close();
            myConnection.Close();
            return usuarios;
        }

        public string ObtenerUsuario(long usuarioId)
        {
            string usuarios = "";
            string query = "SELECT nombre FROM Usuarios WHERE usuarios_id = "+usuarioId;
            MySqlConnection myConnection = new MySqlConnection();
            myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
            myConnection.Open();

            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, myConnection);
            //Execute command
            cmd.ExecuteNonQuery();
            //close connection
            MySqlDataReader dataReader = cmd.ExecuteReader();
            //Read the data and store them in the list
            while (dataReader.Read())
            {

                usuarios = (string)dataReader["nombre"];

            }

            //close Data Reader
            dataReader.Close();
            //close Connection
            myConnection.Close();



            return usuarios;
        }

        public string CambiarContrasena(string idUsuario, string nueva)
        {
            try
            {
                string query = "UPDATE Usuarios SET password = '" + nueva + "' WHERE usuarios_id = " + idUsuario;
                MySqlConnection myConnection = new MySqlConnection();
                myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
                myConnection.Open();

                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, myConnection);
                //Execute command
                cmd.ExecuteNonQuery();

                myConnection.Close();

                return "ok";
            }
            catch (Exception e)
            {
                return "fallo";
            }
        }


        public string ObtenerMail(long usuarioId)
        {
            string mail = "";
            string query = "SELECT mail FROM Usuarios WHERE usuarios_id = " + usuarioId;
            MySqlConnection myConnection = new MySqlConnection();
            myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
            myConnection.Open();

            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, myConnection);
            //Execute command
            cmd.ExecuteNonQuery();
            //close connection
            MySqlDataReader dataReader = cmd.ExecuteReader();
            //Read the data and store them in the list
            while (dataReader.Read())
            {

                mail = (string)dataReader["mail"];

            }

            //close Data Reader
            dataReader.Close();
            //close Connection
            myConnection.Close();



            return mail;

        }


        public List<Usuario> ObtenerTodosUsuarios()
        {
            List<Usuario> usuarios = new List<Usuario>();

            string query = "SELECT * FROM Usuarios";
            MySqlConnection myConnection = new MySqlConnection();
            myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
            myConnection.Open();

            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, myConnection);
            //Execute command
            cmd.ExecuteNonQuery();
            //close connection
            MySqlDataReader dataReader = cmd.ExecuteReader();
            //Read the data and store them in the list
            while (dataReader.Read())
            {
                Usuario usuario = new Usuario();
                usuario.UsuariosId =(long)dataReader["usuarios_id"];
                usuario.JefeId =(long)dataReader["jefe_id"];;
                usuario.Mail =(string)dataReader["mail"];;
                usuario.Nombre =(string)dataReader["nombre"];;
                usuario.Rol = (int)dataReader["rol"]; ;

                usuarios.Add(usuario);

            }

            //close Data Reader
            dataReader.Close();
            //close Connection
            myConnection.Close();

            return usuarios;
        }


        public string GuardarUsuario(Usuario usuario)
        {
           
            try
            {

                string query;

                if (usuario.UsuariosId == 0)
                {
                    query = "INSERT INTO `usuarios` (nombre, password, rol, mail, jefe_id) VALUES ('" + usuario.Nombre + "','"+usuario.Contrasena+"',"+usuario.Rol+
                    ",'"+usuario.Mail+"',"+usuario.JefeId+")";
                }
                else
                {
                    query = "UPDATE usuarios SET nombre = '"+usuario.Nombre+"', rol ="+usuario.Rol +", mail = '"+usuario.Mail+"', jefe_id = "+usuario.JefeId+
                        " WHERE usuarios_id = "+usuario.UsuariosId;
                }

                MySqlConnection myConnection = new MySqlConnection();
                myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
                myConnection.Open();

                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, myConnection);
                //Execute command
                cmd.ExecuteNonQuery();
                //close connection
                myConnection.Close();

                return "ok";

            }
            catch (Exception ex)
            {

                //    _Logger.Error("Error in DeleteCourse()", ex);

                return "error";
            }

        }


        public void BorrarUsuario(string usuarioId)
        {
            try
            {
                // RequireRole(sessionUUID, Role.WRITE_ACCESS, Role.USERS);
                string query = "DELETE FROM `usuarios` WHERE usuarios_id = " + usuarioId;
                MySqlConnection myConnection = new MySqlConnection();
                myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
                myConnection.Open();

                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, myConnection);
                //Execute command
                cmd.ExecuteNonQuery();
                //close connection
                myConnection.Close();
            }
            catch (Exception ex)
            {

                //    _Logger.Error("Error in DeleteCourse()", ex);

                throw ex;
            }
        }
    }